﻿using System;
using System.Data;
using System.Text;
using BP.DA;
using BP.Web;
using BP.En;
using BP.Difference;
using System.IO;
using System.IO.Compression;
using Microsoft.AspNetCore.Http;
using ZipFile = System.IO.Compression.ZipFile;
using NPOI.SS.UserModel;
using DocumentFormat.OpenXml.Office2010.ExcelAc;
using System.Collections.Generic;
using BP.Sys;
using NPOI.Util;
using NPOI.SS.Formula.Functions;

namespace BP.App.NetCore.GZSZ
{
    /// <summary>
    /// 页面功能实体
    /// </summary>
    public class GZ_CommTS : BP.WF.HttpHandler.DirectoryPageBase
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public GZ_CommTS()
        {
        }

        //获取项目进度报表的所有统计
        public object getXmjdCount()
        {
            string searInfo = this.GetRequestVal("searInfo");
            string year = this.GetRequestVal("年度");
            StringBuilder sql = new StringBuilder("");
            sql.Append("select  sum(合同金额) 合同金额,sum(结算金额) 结算金额,sum(期初开票) 期初开票,sum(A1月开票) A1月开票,sum(A2月开票) A2月开票,sum(A3月开票) A3月开票,");
            sql.Append("sum(A4月开票) A4月开票,sum(A5月开票) A5月开票,sum(A6月开票) A6月开票,sum(A7月开票) A7月开票,sum(A8月开票) A8月开票,sum(A9月开票) A9月开票,");
            sql.Append("sum(A10月开票) A10月开票,sum(A11月开票) A11月开票,sum(A12月开票) A12月开票,sum(本年开票) 本年开票,sum(累计开票) 累计开票,sum(期初收款) 期初收款,");
            sql.Append("sum(A1月收款) A1月收款,sum(A2月收款) A2月收款,sum(A3月收款) A3月收款,sum(A4月收款) A4月收款,sum(A5月收款) A5月收款,sum(A6月收款) A6月收款,");
            sql.Append("sum(A7月收款) A7月收款,sum(A8月收款) A8月收款,sum(A9月收款) A9月收款,sum(A10月收款) A10月收款,sum(A11月收款) A11月收款,sum(A12月收款) A12月收款,");
            sql.Append("sum(本年收款) 本年收款,sum(累计收款) 累计收款,sum(期初工作量) 期初工作量,sum(A1月工作量) A1月工作量,sum(A2月工作量) A2月工作量,sum(A3月工作量) A3月工作量,");
            sql.Append("sum(A4月工作量) A4月工作量,sum(A5月工作量) A5月工作量,sum(A6月工作量) A6月工作量,sum(A7月工作量) A7月工作量,sum(A8月工作量) A8月工作量,sum(A9月工作量) A9月工作量,");
            sql.Append("sum(A10月工作量) A10月工作量,sum(A11月工作量) A11月工作量,sum(A12月工作量) A12月工作量,sum(本年工作量) 本年工作量,sum(累计工作量) 累计工作量");
            sql.Append(" from   xmjdb where 1=1 ");
            if (!DataType.IsNullOrEmpty(searInfo))
            {
                sql.Append(" and (合同号 ='").Append(searInfo).Append("' or 合同名称='").Append(searInfo).Append("')");
            }
            if (!DataType.IsNullOrEmpty(year))
            {
                sql.Append(" and 年度 =").Append(year);
            }
            Paras ps = new Paras();
            ps.SQL = sql.ToString();
            DataTable prj = DBAccess.RunSQLReturnTable(ps);

            return BP.Tools.Json.ToJson(prj.Rows[0]);
        }

        /**
	     *  自动登录
	     *
	     * @return
	     * @throws Exception
	     */
        public string getGZUserInfo()
        {
            string User = this.GetRequestVal("User");
            try
            {
                //AES加密key
                string key = SystemConfig.GetIsEncryptionKey();
                //User = BP.Tools.AESHelper.AESDecrypt(User, key);
                BP.Port.WeiXin.Emp emp = new BP.Port.WeiXin.Emp();
                //判断帐号
                if (emp.IsExit(BP.Port.EmpAttr.No, User))
                {
                    String userToken = "";
                    BP.WF.Dev2Interface.Port_Login(emp.No);
                    userToken = BP.WF.Dev2Interface.Port_GenerToken(emp.No);
                    return userToken;
                }
            }
            catch (Exception ex)
            {
                return "err@获取用户出错" + ex.Message;
            }
            return "err@无此用户:" + User;
        }


        /// <summary>
        /// 改变合同流程BillNo
        public object UpdateHTBillNo()
        {
            string xht = this.GetRequestVal("xht");  //现合同编号   S2025-0001
            string zht = this.GetRequestVal("zht");       //主合同编号  S2025-0001
            string ibc = this.GetRequestVal("IsBuChong");  //0:主合同    1：补充合同
            string workID = this.GetRequestVal("workID");
            string xbh = "";
            //判断是否做过编号修改
            string sql = "select ISNULL(BCCount, 0) from  ND2Rpt where  BillNo=" + SystemConfig.AppCenterDBVarStr + "v";
            Paras ps = new Paras();
            ps.SQL = sql;
            ps.Add("v", xht);
            int bcount = DBAccess.RunSQLReturnValInt(ps);
            if (bcount >0)
            {
                return xht;
            }
            if (ibc.Equals("1"))
            {
                //补充合同编号=主合同编号+‘补’+流水号（第几个补充合同）
                sql = " Update PM_HTIncome set BCCount=ISNULL(BCCount, 0)+1 where MyPK=" + SystemConfig.AppCenterDBVarStr + "v";
                ps.SQL = sql;
                ps.Add("v", zht);
                DBAccess.RunSQL(ps);
                sql= "select BCCount from PM_HTIncome where MyPK=" + SystemConfig.AppCenterDBVarStr + "v";
                ps.SQL = sql;
                bcount=DBAccess.RunSQLReturnValInt(ps);
                xbh = zht+ "补" + bcount;
                sql = " Update ND2Rpt set BillNo=" + SystemConfig.AppCenterDBVarStr + "v,BCCount=ISNULL(BCCount, 0)+1 where BillNo=" + SystemConfig.AppCenterDBVarStr + "v1";
                ps.SQL = sql;
                ps.Add("v", xbh);
                ps.Add("v1", xht);
                DBAccess.RunSQL(ps);
                sql = " Update WF_GenerWorkFlow set BillNo=" + SystemConfig.AppCenterDBVarStr + "v where BillNo=" + SystemConfig.AppCenterDBVarStr + "v1";
                ps.SQL = sql;
                DBAccess.RunSQL(ps);
            }
            else
            {
                //主合同数量
                sql = " select count(*)ct from  ND2Rpt where ShiFouBuChongHeTong=0 and BillNo like '"+ xht.Split("-")[0] + "%'";
                ps.SQL = sql;
                bcount = DBAccess.RunSQLReturnValInt(ps);
                int mbcount = int.Parse(xht.Split("-")[1]);
                if (mbcount <= bcount)
                {
                    bcount = mbcount;
                }
                else
                {
                    //主合同最大的编号
                    sql = " select top 1 BillNo from  ND2Rpt where ShiFouBuChongHeTong=0 and BillNo like '" + xht.Split("-")[0] + "%' and BillNo !=" + SystemConfig.AppCenterDBVarStr + "v1 order by BillNo desc";
                    ps.SQL = sql;
                    ps.Add("v1", xht);
                    string mbillNo = DBAccess.RunSQLReturnString(ps);
                    mbcount = int.Parse(mbillNo.Split("-")[1]);
                    if (mbcount >= bcount)
                    {
                        bcount = mbcount + 1;
                    }
                }
                string paddedInput = bcount.ToString().PadLeft(4, '0'); // 前面补零
                xbh = xht.Split("-")[0] + "-"+ paddedInput;
                sql = " Update ND2Rpt set BillNo=" + SystemConfig.AppCenterDBVarStr + "v,BCCount=ISNULL(BCCount, 0)+1 where BillNo=" + SystemConfig.AppCenterDBVarStr + "v1";
                ps.SQL = sql;
                ps.Add("v", xbh);
                DBAccess.RunSQL(ps);
                sql = " Update WF_GenerWorkFlow set BillNo=" + SystemConfig.AppCenterDBVarStr + "v where BillNo=" + SystemConfig.AppCenterDBVarStr + "v1";
                ps.SQL = sql;
                DBAccess.RunSQL(ps);
               
            }
            return xbh;
        }
       
        //获取目标及实现金额
        public object GetYearMb()
        {
            string sql = " SELECT YearType,YearMB,HeTongJinE,ShouKuanJinE,HZXZJE from DataV order by YearType";
            Paras ps = new Paras();
            ps.SQL = sql;
            DataTable prj = DBAccess.RunSQLReturnTable(ps);
            List<List<string>> alist = new List<List<string>>();
            List<string> yearList = new List<string>(); yearList.Add("年度");
            List<string> yearMbList = new List<string>(); yearMbList.Add("年目标");
            List<string> yearHtList = new List<string>(); yearHtList.Add("合同额");
            List<string> yearSkList = new List<string>(); yearSkList.Add("收款额");
            List<string> yearGzList = new List<string>(); yearGzList.Add("工作量");
            foreach (DataRow item in prj.Rows)
            {
                string YearType = item["YearType"].ToString();
                string YearMB = Math.Round(double.Parse(item["YearMB"].ToString()), 2).ToString();
                string HeTongJinE = Math.Round(double.Parse(item["HeTongJinE"].ToString()), 2).ToString();
                string ShouKuanJinE = Math.Round(double.Parse(item["ShouKuanJinE"].ToString()), 2).ToString();
                string HZXZJE = Math.Round(double.Parse(item["HZXZJE"].ToString()),2).ToString();
                yearList.Add(YearType);
                yearMbList.Add(YearMB);
                yearHtList.Add(HeTongJinE);
                yearSkList.Add(ShouKuanJinE);
                yearGzList.Add(HZXZJE);

            }
            alist.Add(yearList);
            alist.Add(yearMbList);
            alist.Add(yearHtList);
            alist.Add(yearSkList);
            alist.Add(yearGzList);
            return BP.Tools.Json.ToJson(alist);
        }
        /// <summary>
        /// 历史合同扫描件的处理
        public object HTSMJ()
        {
            string sql = " SELECT MyPK FROM PM_HTIncome ";
            DataTable prj = DBAccess.RunSQLReturnTable(sql);
            foreach (DataRow item in prj.Rows)
            {
                string MPK = item["MyPK"].ToString();
                move_ath(MPK);
            }
            return "success";
        }

        /// 根据项目id获取项目信息
        public object GetXmInfo()
        {
            string No = this.GetRequestVal("No");
            string sql = " SELECT mypk No, PrjName Name,XMLX,FuZeRen,FuZeRenT,HeTong,HeTongMC," +
              "case XMLX when 0 then '房建' when 1 then '市政'  when 2 then '燃气' when 4 then '敞口' else '轨道' end xmlxName, " +
             "isnull((select sum(HeTongJinE) from PM_HTIncome where XiangMuBianHao=p.mypk),0) glje,prjSta" +
             " FROM PM_Prj p where mypk=" + SystemConfig.AppCenterDBVarStr + "v";
            Paras ps = new Paras();
            ps.SQL = sql;
            ps.Add("v", No);
            DataTable prj = DBAccess.RunSQLReturnTable(ps);
            return BP.Tools.Json.ToJson(prj.Rows[0]);
        }
        /// 根据workid，webno 获取送审图纸从表（ND101TuZhi2）中对应人员的专业信息
        public object GetZhuanYeInfo()
        {
            string WorkID = this.GetRequestVal("WorkID");
            string sql = " SELECT SQL_ZhuanYe AS ZhuanYe,SQL_ZhuanYeT AS ZhuanYeT" +
             " FROM ND101TuZhi2  where RenYuan ='" + WebUser.No + "' and RefPK=" + SystemConfig.AppCenterDBVarStr + "v";
            Paras ps = new Paras();
            ps.SQL = sql;
            ps.Add("v", WorkID);
            DataTable prj = DBAccess.RunSQLReturnTable(ps);
            return BP.Tools.Json.ToJson(prj.Rows[0]);
        }

        /// 项目立项轮休PM_FuWuRY表获取下一个服务人员
        public object GetFuWuRY()
        {
            string pageID = this.GetRequestVal("pageID");
            string tb1 = this.GetRequestVal("tb1");
            string EmpNo = "";
            string EmpNoT = "";
            //非轨道，类型==燃气时，指定服务人员 ：周新红   zhouxh
            if (pageID== "Track1.Track2" && tb1 == "2")
            {
                EmpNo = "zhouxh";
                EmpNoT = "周新红";
            }
            else
            {
                string sql = " SELECT MyPK,EmpNo,EmpNoT,PaiXu,WFStatu FROM PM_FuWuRY ORDER BY PaiXu";
                DataTable prj = DBAccess.RunSQLReturnTable(sql);
                int WFStatu = 0;
                string MyPK = "";
              
                foreach (DataRow item in prj.Rows)
                {
                    int WFS = int.Parse(item["WFStatu"].ToString());
                    string MPK = item["MyPK"].ToString();
                    string ENo = item["EmpNo"].ToString();
                    string ENoT = item["EmpNoT"].ToString();
                    if (MyPK.Equals(""))
                    {
                        MyPK = MPK;
                        EmpNo = ENo;
                        EmpNoT = ENoT;
                    }
                    if (WFS == 1)
                    {
                        WFStatu = WFS;
                    }
                    else if (WFStatu == 1)
                    {
                        MyPK = MPK;
                        EmpNo = ENo;
                        EmpNoT = ENoT;
                        WFStatu = WFS;
                    }
                }
                sql = " update PM_FuWuRY set WFStatu=0";
                DBAccess.RunSQL(sql);
                sql = " update PM_FuWuRY set WFStatu=1 where MyPK='" + MyPK + "'";
                DBAccess.RunSQL(sql);
            }
           
            return EmpNo + "," + EmpNoT;
        }
        /// 创建项目总工作量时获取提前存储的送审工作量
        public object GetSongShens()
        {
            //项目名称
            string XiangMuNo = this.GetRequestVal("XiangMuNo");
            string sql = " SELECT JieDuanNo,worknodeno,sum(BenCiSongShenLiang)BenCiSongShenLiang FROM PM_WorkDtlSS where XiangMuNo=" + SystemConfig.AppCenterDBVarStr + "v group by JieDuanNo,worknodeno";
            Paras ps = new Paras();
            ps.SQL = sql;
            ps.Add("v", XiangMuNo);
            DataTable prj = DBAccess.RunSQLReturnTable(ps);
            return BP.Tools.Json.ToJson(prj.Rows);
        }
        /// 项目是否已经创建过合同
        public object IsCreateHTByXM()
        {
            //项目名称
            string XiangMuNo = this.GetRequestVal("XiangMuNo");
            string sql = " SELECT count(1) FROM PM_HTIncome where XiangMuBianHao=" + SystemConfig.AppCenterDBVarStr + "v ";
            Paras ps = new Paras();
            ps.SQL = sql;
            ps.Add("v", XiangMuNo);
            int prj = DBAccess.RunSQLReturnValInt(ps);
            return BP.Tools.Json.ToJson(prj);
        }
        /// 根据合同编号获取之前所有的开票信息
        public object queryKaiPiaosByHT()
        {
            //项目名称
            string HtBH = this.GetRequestVal("HtBH");
            string sql = " SELECT ZhuHeTongBianHao,HeTongBianHao,HeTongMingChen,KaiPiaoRiQi,KaiPiaoShenQingJinE,ZHTLJYDZJE FROM PM_KaiPiaoSQ where HeTongBianHao=" + SystemConfig.AppCenterDBVarStr + "v  order by KaiPiaoRiQi desc ";
            Paras ps = new Paras();
            ps.SQL = sql;
            ps.Add("v", HtBH);
            DataTable prj = DBAccess.RunSQLReturnTable(ps);
            return BP.Tools.Json.ToJson(prj.Rows);
        }

        /// 送审报表查询
        public object GetSongShenForm()
        {
            //项目名称        // and PrjNo=" + SystemConfig.AppCenterDBVarStr + "v ";       //ps.Add("v", PrjNo);
            string PrjNo = this.GetRequestVal("PrjNo");
            string wheresql = " (1=1) ";

            if (!(WebUser.Name == "曾俊杰" || WebUser.Name == "陈水盛" || WebUser.Name == "章亚青" || WebUser.Name == "admin"))
            { wheresql = " (RenYuanT='" + WebUser.Name + "' or FuZeRenT='" + WebUser.Name + "')  "; 
            }
             

            string sql = @" SELECT  CASE a.WFState WHEN 0 THEN '空白' WHEN 1 THEN '草稿' WHEN 2 THEN '运行中' WHEN 3 THEN '已完成' 
                        WHEN 4 THEN '挂起' WHEN 5 THEN '退回' WHEN 6  THEN '转发' WHEN 7 THEN '删除' WHEN 8 THEN '加签' WHEN 9 THEN '冻结' 
                        WHEN 10 THEN '批处理' WHEN 11 THEN '加签回复状态' END WFState ,PrjNo ,PrjName,zixiangmingchen,b.ZhuanYeT,b.TuZhiShuLiang,
                        CONVERT(DATE,a.RDT) RDT,a.JiHuaWanChengShiJian" +
                ",DATEDIFF(DAY,a.RDT,a.JiHuaWanChengShiJian) tjsj,b.RenYuanT,a.FuZeRenT,a.JSDWMCT,a.JSDW_Linker,a.JSDW_Tel,a.SJDWMCT,a.SJDW_Linker,a.SJDW_Tel" +
                ",a.KCSWMCT,a.KCDW_Linker,a.KCDW_Tel " +
                " FROM dbo.ND12Rpt a LEFT OUTER JOIN  ND101TuZhi2 b ON a.OID=b.FID " +
                " WHERE  "+ wheresql + " and ISNULL(a.JiHuaWanChengShiJian,'')<>''";
            Paras ps = new Paras();
            ps.SQL = sql;
            DataTable prj = DBAccess.RunSQLReturnTable(ps);
            return BP.Tools.Json.ToJson(prj.Rows);
        }
        /// 根据流程编号和workid 获取所有的流程参与人员的No
        public string GetNOsByFlowWorkID()
        {
            string WorkID = this.GetRequestVal("WorkID");
            string FlowNo = this.GetRequestVal("FlowNo");
            FlowNo = "ND"+FlowNo.Replace("0", "")+"track";
            string sql = " SELECT EmpFrom,EmpTo from " + FlowNo + " where WorkID=" + SystemConfig.AppCenterDBVarStr + "v1";
            Paras ps = new Paras();
            ps.SQL = sql;
            ps.Add("v1", WorkID);
            DataTable dtt = DBAccess.RunSQLReturnTable(ps);
            string rstr="";
            foreach (DataRow dr in dtt.Rows)
            {
                rstr += dr[0].ToString() + ","+ dr[1].ToString()+",";
            }
            return rstr;
        }

        //导入数据
        public object PM_ImportByClear()
        {
            string msg = "";
            try
            {
                string rtype = PM_ImportTypeCheck();
                if ( !rtype.Equals(""))
                {
                    return rtype;
                }
                var files = HttpContextHelper.RequestFiles();
                string fileName = System.IO.Path.GetFileName(files[0].FileName);
                string type = this.GetRequestVal("type");
                string impType = this.GetRequestVal("impType");
                string ext = ".zip";
                //excel
                if (impType.Equals("0") )
                {
                    ext = ".xlsx";
                    //设置文件名
                    string fileNewName = DateTime.Now.ToString("yyyyMMddHHmmssff") + ext;
                    //文件存放路径
                    string filePath = BP.Difference.SystemConfig.PathOfTemp + fileNewName;
                    HttpContextHelper.UploadFile(files[0], filePath);
                    if (type.Equals("Prj"))
                    {
                        msg= Prj_Import(filePath);
                    }
                    else if (type.Equals("DanWei"))
                    {
                        msg = DanWei_Import(filePath);
                    }
                    else if (type.Equals("ZHTIncome"))//合同
                    {
                        msg = ZHTIncome2_Import(filePath);
                    }
                    else if (type.Equals("KaiPiaoSQ"))//开票申请
                    {
                        msg = KaiPiaoSQ2_Import(filePath);
                    }
                    else if (type.Equals("FaPiaoTZ"))//发票台账
                    {
                        msg = FaPiaoTZ_Import(filePath);
                    }
                    else if (type.Equals("PrjZGZL"))//工作总量
                    {
                        msg = PrjZGZL_Import(filePath);
                    }

                    
                }
                else  //zip文件
                {
                    //文件存放路径
                    string zipFlod = BP.Difference.SystemConfig.PathOfTemp + "GZSZ/" + DateTime.Now.ToString("yyyyMMddHHmmssff") + "/";
                    //zip解压后文件夹
                    String zipFlodExt = SystemConfig.PathOfDataUser + "UploadFile/GZSZ/" + DateTime.Now.ToString("yyyyMMddHHmmssff") + "/";
                    // 如果文件夹不存在，则创建文件夹
                    if (System.IO.Directory.Exists(zipFlodExt) == false)
                      System.IO.Directory.CreateDirectory(zipFlodExt);

                    string fname = DateTime.Now.ToString("yyyyMMddHHmmssff") + ext;
                    string zipFile = zipFlod + fname;
                    HttpContextHelper.UploadFile(files[0], zipFile);
                    //zip解压前 注册代码页提供器实例
                    Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
                    Uncompress(zipFile, zipFlodExt);
                    if (type.Equals("PrjSSLC"))  //送审
                    {
                        msg = PrjSSLC_Import(zipFlodExt);
                    }
                    else if (type.Equals("ZHTIncome"))//合同
                    {
                        msg = ZHTIncome_Import(zipFlodExt);
                    }
                    else if (type.Equals("KaiPiaoSQ"))//开票申请
                    {
                        msg = KaiPiaoSQ_Import(zipFlodExt);
                    }
                }
            }catch (Exception e)
            {
                return "err@导入失败"+e.Message;
            }
            if (msg.Equals(""))
            {
                return "info@导入成功";
            }
            else
            {
                return msg;
            }

        }
        //工作总量导入
        public string PrjZGZL_Import(string filePath)
        {
            //主数据处理
            DataTable dt0Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(filePath);
            string cls0 = "TS.PM.PrjZGZL";
            string cls1 = "TS.PM.WorkDtl";
            string msg = "";
            string existNo = ",";
            string[] sf = new string[] { "否", "是" };
            string[] fplx = new string[] { "数电票(普通发票)", "数电票(增值税专用发票)", "增值税普通发票", "增值税专用发票" };
            string[] sl = new string[] { "0%", "1%", "3%", "6%" };
            string[] jieduan = new string[] { "车站", "区间", "系统" };
            DataTable jiedians = DBAccess.RunSQLReturnTable("select No,Name,JieDuanNo from PM_WorkNode");
            int i = 0;
            string PrjNo = "";
            foreach (DataRow dr in dt0Rows.Rows)
            {
                
                if (i == 0)
                {
                    string? MyPK = dr["项目编号"] as string; //项目编号
                    if (MyPK == null || MyPK.Equals(""))
                    {
                        msg += " err@编号:对应的项目编号不能为空";
                        break;
                    }
                    TSEntityMyPK en = new TSEntityMyPK(cls0);
                    en.MyPK = MyPK;
                    PrjNo = MyPK;
                    int rct = en.RetrieveFromDBSources();
                    string? PrjName = dr["项目名称"] as string; //项目名称
                    en.SetValByKey("PrjName", PrjName);
                    if (rct > 0)
                    {
                        //en.Update();
                    }
                    else
                    {
                        en.Insert();
                    }
                    TSEntitiesMyPK endtls = new TSEntitiesMyPK(cls1);
                    endtls.Delete("PrjNo", PrjNo);
                }
                TSEntityMyPK endtl = new TSEntityMyPK(cls1);
                endtl.SetValByKey("PrjNo", PrjNo);

                string? JieDuanNoT = dr["阶段"] as string; //阶段
                int jdmcindex = queryIndex(JieDuanNoT, jieduan);
                endtl.SetValByKey("JieDuanNoT", JieDuanNoT);
                endtl.SetValByKey("JieDuanNo", jdmcindex);

                string? WorkNodeNoT = dr["节点"] as string; //节点
                string nindex = queryIndexByTableAndOther(WorkNodeNoT, "JieDuanNo", jdmcindex.ToString(), jiedians);
                endtl.SetValByKey("WorkNodeNoT", WorkNodeNoT);
                endtl.SetValByKey("WorkNodeNo", nindex);
                string? JieDianZiXian = dr["节点子项"] as string; //节点子项
                endtl.SetValByKey("JieDianZiXian", JieDianZiXian);
                string? BiLv = dr["进度比例"] as string; //进度比例
                endtl.SetValByKey("BiLv", return0ByEmpt(BiLv));

                string? NumOfAll = dr["总个数（长度）"] as string; //总个数（长度）
                endtl.SetValByKey("NumOfAll", return0ByEmpt(NumOfAll));

                string? EeLingWC = dr["2020年完成个数（长度）"] as string; //2020年完成个数（长度）
                endtl.SetValByKey("EeLingWC", return0ByEmpt(EeLingWC));
                string? EeLingYiWC = dr["2021年完成个数（长度）"] as string; //2021年完成个数（长度）
                endtl.SetValByKey("EeLingYiWC", return0ByEmpt(EeLingYiWC));
                string? EeLingEeWC = dr["2022年完成个数（长度）"] as string; //2020年完成个数（长度）
                endtl.SetValByKey("EeLingEeWC", return0ByEmpt(EeLingEeWC));
                string? EeLingSanWC = dr["2023年完成个数（长度）"] as string; //2020年完成个数（长度）
                endtl.SetValByKey("EeLingSanWC", return0ByEmpt(EeLingSanWC));
                string? EeLingSiWC = dr["2024年完成个数（长度）"] as string; //2020年完成个数（长度）
                endtl.SetValByKey("EeLingSiWC", return0ByEmpt(EeLingSiWC));
                string? YiWanChengGeShu = dr["累计完成个数（长度）"] as string; //累计完成个数（长度）
                endtl.SetValByKey("YiWanChengGeShu", return0ByEmpt(YiWanChengGeShu));
                string? EeLingQR = dr["2020年确认工作量比例"] as string; //2020年确认工作量比例
                endtl.SetValByKey("EeLingQR", return0ByEmpt(EeLingQR));
                string? EeLingYiQR = dr["2021年确认工作量比例"] as string; //2020年确认工作量比例
                endtl.SetValByKey("EeLingYiQR", return0ByEmpt(EeLingYiQR));
                string? EeLingEeQR = dr["2022年确认工作量比例"] as string; //2020年确认工作量比例
                endtl.SetValByKey("EeLingEeQR", return0ByEmpt(EeLingEeQR));
                string? EeLingSanQR = dr["2023年确认工作量比例"] as string; //2020年确认工作量比例
                endtl.SetValByKey("EeLingSanQR", return0ByEmpt(EeLingSanQR));
                string? EeLingSiQR = dr["2024年确认工作量比例"] as string; //2020年确认工作量比例
                endtl.SetValByKey("EeLingSiQR", return0ByEmpt(EeLingSiQR));
                string? JinDu = dr["累计确认工作量比例"] as string; //累计确认工作量比例
                endtl.SetValByKey("JinDu", return0ByEmpt(JinDu));
                endtl.Insert();

                i++;
            }
            return msg;
        }

        //发票台账导入
        public string FaPiaoTZ_Import(string filePath)
        {
            //主数据处理
            DataTable dt0Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(filePath);
            string cls0 = "TS.PM.FaPiaoTZ";
            string msg = "";
            string existNo = ",";
            string[] sf = new string[] { "否", "是" };
            string[] fplx = new string[] { "数电票(普通发票)", "数电票(增值税专用发票)", "增值税普通发票", "增值税专用发票" };
            string[] sl = new string[] { "0%", "1%", "3%", "6%" };
            string[] sl2 = new string[] { "0", "0.01", "0.03", "0.06" };
            DataTable danweis = DBAccess.RunSQLReturnTable("select * from PM_DanWei");
            foreach (DataRow dr in dt0Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls0);
               
                string? FaPiaoBianHao = dr["发票编号"] as string; //发票编号
                if (FaPiaoBianHao == null || FaPiaoBianHao.Equals(""))
                {
                    msg += " err@编号:对应的发票编号不能为空";
                    continue;
                }
                en.MyPK = FaPiaoBianHao;
                int rct = en.RetrieveFromDBSources();
                en.SetValByKey("FaPiaoBianHao", FaPiaoBianHao);

                string? KaiPiaoRiQi = dr["开票日期"] as string; //开票日期
                en.SetValByKey("KaiPiaoRiQi", KaiPiaoRiQi);
                string? ShuiLu = dr["税率"] as string; //税率
                en.SetValByKey("ShuiLu", queryIndex(ShuiLu,sl2));
                string? ShuiE = dr["税额"] as string; //税额
                en.SetValByKey("ShuiE", ShuiE);
                string? FaPiaoJinEBHS = dr["发票金额不含税"] as string; //发票金额不含税
                en.SetValByKey("FaPiaoJinEBHS", FaPiaoJinEBHS);
                string? ShanWeiMingChenT = dr["收款单位"] as string; //收款单位
                string ShanWeiMingChen = queryIndexByTable(ShanWeiMingChenT, danweis);
                en.SetValByKey("ShanWeiMingChenT", ShanWeiMingChenT);
                en.SetValByKey("ShanWeiMingChen", ShanWeiMingChen);
                string? HeTongBianHao = dr["合同编号"] as string; //合同编号
                en.SetValByKey("HeTongBianHao", HeTongBianHao);
                string? FaPiaoJinE = dr["发票金额(元)"] as string; //发票金额(元)
                en.SetValByKey("FaPiaoJinE", return0ByEmpt(FaPiaoJinE));
                string? YiShouKuan = dr["已收款金额"] as string; //已收款金额
                en.SetValByKey("YiShouKuan", return0ByEmpt(YiShouKuan));
                string? WeiShouKuan = dr["未收款金额"] as string; //未收款金额
                en.SetValByKey("WeiShouKuan", return0ByEmpt(WeiShouKuan));

                if (rct>0)
                {
                    en.Update();
                }
                else
                {
                    en.Insert();
                }
            }
            return msg;
        }
        //开票申请导入
        public string KaiPiaoSQ2_Import(string filePath)
        {
            //主数据处理
            DataTable dt0Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(filePath);
            string cls0 = "TS.PM.KaiPiaoSQ";
            string msg = "";
            string existNo = ",";
            string[] sf = new string[] { "否", "是" };
            string[] fplx = new string[] { "数电票(普通发票)", "数电票(增值税专用发票)", "增值税普通发票", "增值税专用发票" };
            string[] sl = new string[] { "0%", "1%", "3%", "6%" };
            DataTable danweis = DBAccess.RunSQLReturnTable("select * from PM_DanWei");
            foreach (DataRow dr in dt0Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls0);
                en.MyPK = dr[0].ToString();

                string? HeTongBianHao = dr["合同编号"] as string; //合同编号
                if (HeTongBianHao == null || HeTongBianHao.Equals(""))
                {
                    msg += " err@编号:" + en.MyPK + "对应的合同编号不能为空";
                    continue;
                }
                int rct = en.RetrieveFromDBSources();
                en.SetValByKey("HeTongBianHao", HeTongBianHao);
                string? HeTongMingChen = dr["合同名称"] as string; //合同名称
                en.SetValByKey("HeTongMingChen", HeTongMingChen);
                string? KaiPiaoShenQingRen = dr["开票申请人"] as string; //开票申请人
                en.SetValByKey("KaiPiaoShenQingRen", KaiPiaoShenQingRen);
                string? KaiPiaoShenQingRenBH = dr["登录账号"] as string; //登录账号
                en.SetValByKey("KaiPiaoShenQingRenBH", KaiPiaoShenQingRenBH);

                string? KaiPiaoRiQi = dr["开票申请日期"] as string; //开票申请日期
                en.SetValByKey("KaiPiaoRiQi", KaiPiaoRiQi);

                string? XiangMuMingChen = dr["项目名称"] as string; //项目名称
                en.SetValByKey("XiangMuMingChen", XiangMuMingChen);
                string? ZhuHeTongBianHao = dr["主合同编号"] as string; //主合同编号
                en.SetValByKey("ZhuHeTongBianHao", ZhuHeTongBianHao);
                string? JSDWMC = dr["建设单位名称"] as string; //建设单位名称
                en.SetValByKey("JSDWMC", JSDWMC);
                string? XHTJE = dr["现合同金额"] as string; //现合同金额
                en.SetValByKey("XHTJE", return0ByEmpt(XHTJE));
                string? HeTongQianDingRiQi = dr["合同签订日期"] as string; //合同签订日期
                en.SetValByKey("HeTongQianDingRiQi", HeTongQianDingRiQi);
                string? YuJiHuiKuanRiQi = dr["预计回款日期"] as string; //预计回款日期
                en.SetValByKey("YuJiHuiKuanRiQi", YuJiHuiKuanRiQi);
                string? ZHTLJYKPJE = dr["主合同截止上一次累计已开票金额"] as string; //主合同截止上一次累计已开票金额
                en.SetValByKey("ZHTLJYKPJE", return0ByEmpt(ZHTLJYKPJE));
                string? ZHTLJYDZJE = dr["主合同截止上一次累计已到账金额"] as string; //主合同截止上一次累计已到账金额
                en.SetValByKey("ZHTLJYDZJE", return0ByEmpt(ZHTLJYDZJE));
                string? ZHTKGZJLJYQRGZL = dr["主合同截止上一次累计已确认金额"] as string; //主合同截止上一次累计已确认金额
                en.SetValByKey("ZHTKGZJLJYQRGZL", return0ByEmpt(ZHTKGZJLJYQRGZL));
                string? SFJSK = dr["是否结算款"] as string; //是否结算款
                int sfjskindex = queryIndex(SFJSK, sf);
                en.SetValByKey("SFJSK", sfjskindex);
                string? JieSuanJinE = dr["结算金额"] as string; //结算金额
                en.SetValByKey("JieSuanJinE", return0ByEmpt(JieSuanJinE));
                string? HeTongShouFeiTiaoKua = dr["合同收费条款"] as string; //合同收费条款
                en.SetValByKey("HeTongShouFeiTiaoKua", HeTongShouFeiTiaoKua);
                string? FaPiaoLeiXing = dr["发票类型"] as string; //发票类型
                int fplxindex = queryIndex(FaPiaoLeiXing, fplx);
                en.SetValByKey("FaPiaoLeiXing", fplxindex);
                string? KaiPiaoShenQingJinE = dr["开票申请金额"] as string; //开票申请金额
                en.SetValByKey("KaiPiaoShenQingJinE", return0ByEmpt(KaiPiaoShenQingJinE));

                string? ShiFouLianHeTi = dr["是否联合体"] as string; //是否联合体
                int sflhtindex = queryIndex(ShiFouLianHeTi, sf);
                en.SetValByKey("ShiFouLianHeTi", sflhtindex);
                string? ShiFouDaiShouDaiFuXi = dr["是否代收代付性质"] as string; //是否代收代付性质
                int sfdsdfindex = queryIndex(ShiFouDaiShouDaiFuXi, sf);
                en.SetValByKey("ShiFouDaiShouDaiFuXi", sfdsdfindex);
                string? BenCiShouRuJinE = dr["归属我方金额"] as string; //归属我方金额
                en.SetValByKey("BenCiShouRuJinE", return0ByEmpt(BenCiShouRuJinE));
                string? BenCiBoFuJinE = dr["归属联合体方金额"] as string; //归属联合体方金额
                en.SetValByKey("BenCiBoFuJinE", return0ByEmpt(BenCiBoFuJinE));
                string? ShanWeiMingChenT = dr["单位名称"] as string; //单位名称
                string ShanWeiMingChen = queryIndexByTable(ShanWeiMingChenT, danweis);
                en.SetValByKey("ShanWeiMingChenT", ShanWeiMingChenT);
                en.SetValByKey("ShanWeiMingChen", ShanWeiMingChen);
                
                if (rct>0)
                {
                    en.Update();
                }
                else
                {
                    en.Insert();
                }

            }
            return msg;
        }
            //收入合同导入（根据历史数据调整）
        public string ZHTIncome2_Import(string filePath)
        {
            //主数据处理
            DataTable dt0Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(filePath);
            string cls0 = "TS.PM.ZHTIncome";
            string msg = "";
            string existNo = ",";
            string[] sf = new string[] { "否", "是" };
            string[] xmlxs = new string[] { "房建", "市政", "燃气", "轨道", "敞口" };
            string[] status = new string[] { "履行中", "暂停", "终止" };
            string[] htly = new string[] { "委托", "投标", "其他" };
            string[] pslb = new string[] { "会签", "会议" };
            string[] yhtbgfs = new string[] { "增多", "减少" };
            string[] gzlzxlx = new string[] { "十万以下非轨道", "敞口", "十万以上非轨道" };
            string[] htsflx = new string[] { "定金", "进度款", "结算款" };
            string[] dydjd = new string[] { "中期成果", "最终成果" };
            string[] sl = new string[] { "0%", "1%", "3%", "6%" };
            DataTable danweis = DBAccess.RunSQLReturnTable("select * from PM_DanWei");
            DataTable zbbm = DBAccess.RunSQLReturnTable("SELECT No,Name, ParentNo FROM Port_Dept");
            foreach (DataRow dr in dt0Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls0);
                string? No = dr["合同编号处理"] as string; //合同编号处理
                if (No == null || No.Equals(""))
                {
                    continue;
                }

                en.MyPK = No;
                int rct = en.RetrieveFromDBSources();
                string? name = dr["合同名称"] as string; //合同名称
                en.SetValByKey("name", name);
                en.SetValByKey("HeTongMingChen", name);

                //string? XiangMuBianHao = dr["项目编号"] as string;
                // 项目编号
                //if (XiangMuBianHao != null && !XiangMuBianHao.Equals(""))
                // {
                //    string? XiangMuMingChen = dr["项目名称"] as string; //项目名称
                //    en.SetValByKey("XiangMuMingChen", XiangMuMingChen == null ? "" : XiangMuMingChen);
                //    en.SetValByKey("XiangMuBianHao", XiangMuBianHao == null ? "" : XiangMuBianHao);
                //}
                // string? HeTongJinE = dr["合同金额"] as string; //合同金额
                // en.SetValByKey("hetongjine", return0ByEmpt(HeTongJinE));
                //string? JieSuanJinE = dr["结算金额"] as string; //结算金额
                //en.SetValByKey("JieSuanJinE", return0ByEmpt(JieSuanJinE));
                //string? ZHTLJYKPJE = dr["主合同累计已开票金额"] as string; //主合同累计已开票金额
                //en.SetValByKey("ZHTLJYKPJE", return0ByEmpt(ZHTLJYKPJE));
                //string? ZHTLJYDZJE = dr["主合同累计已到帐金额"] as string; //主合同累计已到帐金额
                //en.SetValByKey("ZHTLJYDZJE", return0ByEmpt(ZHTLJYDZJE));
                string? ZHTKGZJLJYQRGZL = dr["主合同开工至今累计已确认工作量"] as string; //主合同开工至今累计已确认工作量
                if (ZHTKGZJLJYQRGZL != null && !ZHTKGZJLJYQRGZL.Equals("") && !ZHTKGZJLJYQRGZL.Equals("0"))
                {
                    en.SetValByKey("ZHTKGZJLJYQRGZL", return0ByEmpt(ZHTKGZJLJYQRGZL));
                }
               
                //string? GZLERLINGERSI = dr["2024年确认工作量金额"] as string; //2024年确认工作量金额
                //en.SetValByKey("GZLERLINGERSI", return0ByEmpt(GZLERLINGERSI));
                //string? GZLERLINGERSAN = dr["截止2023年12月31日累计收入"] as string; //截止2023年12月31日累计收入
                //en.SetValByKey("GZLERLINGERSAN", return0ByEmpt(GZLERLINGERSAN));

                //string? QianDingRiQi = dr["签订日期"] as string; //签订日期
                //en.SetValByKey("QianDingRiQi", QianDingRiQi);
                //string? HTIncomeSta = dr["状态"] as string; //状态
                //int htsta = queryIndex(HTIncomeSta, status);
                //en.SetValByKey("HTIncomeSta", 0);

                //string? JianSheShanWeiT = dr["甲方单位"] as string; //甲方单位
                //string JianSheShanWei = queryIndexByTable(JianSheShanWeiT, danweis);
                //en.SetValByKey("JianSheShanWeiT", JianSheShanWeiT);
                //en.SetValByKey("JianSheShanWei", JianSheShanWei);
                //en.SetValByKey("ShiFouBuChongHeTong", 0);//是否补充合同
                //en.SetValByKey("ShiFouLianHeTi", 0); //是否联合体
                //en.SetValByKey("SFYXZHTJE", 0); //是否影响主合同金额
                //string? ShouRuHeTongLeiXing = dr["收入子项目类型"] as string; //收入子项目类型
                //int srindex = queryIndex(ShouRuHeTongLeiXing, gzlzxlx);
                //en.SetValByKey("ShouRuHeTongLeiXing", srindex);

                if (rct>0)
                {
                    en.Update();
                }
                else
                {
                    en.Insert();
                }
              

                //附件的处理
               // string noyear = en.No.Split("-")[0];
               // string noxh = en.No.Split("-")[1];
               // move_ath(noyear,noxh);
                
            }
            return msg;
        }
        //单位数据导入
        public string DanWei_Import(string filePath)
        {
            DataTable dtRows = BP.DA.DBLoad.ReadExcelFileToDataTable(filePath);
            string cls = "TS.PM.DanWei";
            string msg = "";
            string existNo = ",";
            string[] flmc = new string[] { "建设单位", "设计单位", "勘察单位" };
            foreach (DataRow dr in dtRows.Rows)
            {
                TSEntityNoName en = new TSEntityNoName(cls);
                string Name = dr["客户名称"].ToString();
                string sql = "select no from PM_DanWei where name='" + Name + "'";
                string no = DBAccess.RunSQLReturnString(sql);
                if (no != null && !no.Equals(""))
                {
                    en.No = no;
                    en.RetrieveFromDBSources();
                }
                en.SetValByKey("Name", Name);
                string? DWLX = dr["客户分类名称"] as string; //客户分类名称
                int flindex = queryIndex(DWLX, flmc);
                en.SetValByKey("DWLX", flindex);
                string? SHB = dr["统一社会信用代码/纳税人识别号"] as string; //统一社会信用代码/纳税人识别号
                en.SetValByKey("SHB", SHB);
                string? Addr = dr["地址"] as string; //地址
                en.SetValByKey("Addr", Addr);
                string? Tel = dr["电话"] as string; //电话
                en.SetValByKey("Tel", Tel);
                string? BankName = dr["开户行名称"] as string; //开户行名称
                en.SetValByKey("BankName", BankName);
                string? BankNum = dr["银行账号"] as string; //银行账号
                en.SetValByKey("BankNum", BankNum);
                string? Email = dr["联系邮箱"] as string; //联系邮箱
                en.SetValByKey("Email", Email);

                if (no != null && !no.Equals(""))
                {
                    en.Update();
                }
                else
                {
                    en.Insert();
                }
              
            }
            //联系人
            DataTable dt1Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(filePath, 1);
            string cls1 = "TS.PM.Contact";
            foreach (DataRow dr in dt1Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls1);
                string? ssName = dr["客户名称"] as string; //客户名称
                if (existNo.Contains("," + ssName + ","))
                {
                    continue;
                }
                string sql = "select no from PM_DanWei where name='"+ ssName + "'";
                string no = DBAccess.RunSQLReturnString(sql);
                if(no==null || no.Equals(""))
                {
                    msg += "info@联系人对应单位[" + ssName + "]不存在.";
                    continue;
                }
                en.SetValByKey("No", no);
                string? Linker = dr["联系人"] as string; // 联系人
                en.SetValByKey("Linker", Linker);
                string? Tel = dr["联系电话"] as string; // 联系电话
                en.SetValByKey("Tel", Tel);
                en.Insert();
               
            }
            return msg;
        }
        //开票申请导入
        public string KaiPiaoSQ_Import(string zipFlodExt)
        {
            string fullName = Get_ZipExcelFile(zipFlodExt);
            if (fullName == "")
            {
                return "err@文件不符合约定的格式，没有找到根目录下的文件.";
            }
            //主数据处理
            DataTable dt0Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName);
            string cls0 = "TS.PM.KaiPiaoSQ";
            string msg = "";
            string existNo = ",";
            string[] sf = new string[] { "否", "是" };
            string[] fplx = new string[] { "数电票(普通发票)", "数电票(增值税专用发票)", "增值税普通发票", "增值税专用发票" };
            string[] sl = new string[] { "0%", "1%", "3%", "6%" };
            DataTable danweis = DBAccess.RunSQLReturnTable("select * from PM_DanWei");
            foreach (DataRow dr in dt0Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls0);
                en.MyPK = dr[0].ToString();
               
                string? HeTongBianHao = dr["合同编号"] as string; //合同编号
                if (HeTongBianHao == null || HeTongBianHao.Equals(""))
                {
                    msg += " err@编号:" + en.MyPK + "对应的合同编号不能为空";
                    continue;
                }
                int rct = en.RetrieveFromDBSources();
                en.SetValByKey("HeTongBianHao", HeTongBianHao);
                string? HeTongMingChen = dr["合同名称"] as string; //合同名称
                en.SetValByKey("HeTongMingChen", HeTongMingChen);
                string? KaiPiaoShenQingRen = dr["开票申请人"] as string; //开票申请人
                en.SetValByKey("KaiPiaoShenQingRen", KaiPiaoShenQingRen);
                string? KaiPiaoShenQingRenBH = dr["登录账号"] as string; //登录账号
                en.SetValByKey("KaiPiaoShenQingRenBH", KaiPiaoShenQingRenBH);

                string? KaiPiaoRiQi = dr["开票申请日期"] as string; //开票申请日期
                en.SetValByKey("KaiPiaoRiQi", KaiPiaoRiQi);

                string? XiangMuMingChen = dr["项目名称"] as string; //项目名称
                en.SetValByKey("XiangMuMingChen", XiangMuMingChen);
                string? ZhuHeTongBianHao = dr["主合同编号"] as string; //主合同编号
                en.SetValByKey("ZhuHeTongBianHao", ZhuHeTongBianHao);
                string? JSDWMC = dr["建设单位名称"] as string; //建设单位名称
                en.SetValByKey("JSDWMC", JSDWMC);
                string? XHTJE = dr["现合同金额"] as string; //现合同金额
                en.SetValByKey("XHTJE",return0ByEmpt(XHTJE));
                string? HeTongQianDingRiQi = dr["合同签订日期"] as string; //合同签订日期
                en.SetValByKey("HeTongQianDingRiQi", HeTongQianDingRiQi);
                string? YuJiHuiKuanRiQi = dr["预计回款日期"] as string; //预计回款日期
                en.SetValByKey("YuJiHuiKuanRiQi", YuJiHuiKuanRiQi);
                string? ZHTLJYKPJE = dr["主合同截止上一次累计已开票金额"] as string; //主合同截止上一次累计已开票金额
                en.SetValByKey("XHTJE", return0ByEmpt(ZHTLJYKPJE));
                string? ZHTLJYDZJE = dr["主合同截止上一次累计已到账金额"] as string; //主合同截止上一次累计已到账金额
                en.SetValByKey("XHTJE", return0ByEmpt(ZHTLJYDZJE));
                string? ZHTKGZJLJYQRGZL = dr["主合同截止上一次累计已确认金额"] as string; //主合同截止上一次累计已确认金额
                en.SetValByKey("XHTJE", return0ByEmpt(ZHTKGZJLJYQRGZL));
                string? SFJSK = dr["是否结算款"] as string; //是否结算款
                int sfjskindex = queryIndex(SFJSK,sf);
                en.SetValByKey("SFJSK", sfjskindex);
                string? JieSuanJinE = dr["结算金额"] as string; //结算金额
                en.SetValByKey("JieSuanJinE", return0ByEmpt(JieSuanJinE));
                string? HeTongShouFeiTiaoKua = dr["合同收费条款"] as string; //合同收费条款
                en.SetValByKey("HeTongShouFeiTiaoKua", HeTongShouFeiTiaoKua);
                string? FaPiaoLeiXing = dr["发票类型"] as string; //发票类型
                int fplxindex = queryIndex(FaPiaoLeiXing,fplx);
                en.SetValByKey("FaPiaoLeiXing", fplxindex);
                string? KaiPiaoShenQingJinE = dr["开票申请金额"] as string; //开票申请金额
                en.SetValByKey("KaiPiaoShenQingJinE", return0ByEmpt(KaiPiaoShenQingJinE));

                string? ShiFouLianHeTi = dr["是否联合体"] as string; //是否联合体
                int sflhtindex = queryIndex(ShiFouLianHeTi, sf);
                en.SetValByKey("ShiFouLianHeTi", sflhtindex);
                string? ShiFouDaiShouDaiFuXi = dr["是否代收代付性质"] as string; //是否代收代付性质
                int sfdsdfindex = queryIndex(ShiFouDaiShouDaiFuXi, sf);
                en.SetValByKey("ShiFouDaiShouDaiFuXi", sfdsdfindex);
                string? BenCiShouRuJinE = dr["归属我方金额"] as string; //归属我方金额
                en.SetValByKey("BenCiShouRuJinE", return0ByEmpt(BenCiShouRuJinE));
                string? BenCiBoFuJinE = dr["归属联合体方金额"] as string; //归属联合体方金额
                en.SetValByKey("BenCiBoFuJinE", return0ByEmpt(BenCiBoFuJinE));
                string? ShanWeiMingChenT = dr["单位名称"] as string; //单位名称
                string ShanWeiMingChen = queryIndexByTable(ShanWeiMingChenT,danweis);
                en.SetValByKey("ShanWeiMingChenT", ShanWeiMingChenT);
                en.SetValByKey("ShanWeiMingChen", ShanWeiMingChen);
                if (ShanWeiMingChen!=null && !ShanWeiMingChen.Equals(""))
                {
                    string sql = "select shb,bankName,bankNum,addr,tel,email   from PM_DanWei where Name='" + ShanWeiMingChenT + "'";
                    DataTable xmTable = DBAccess.RunSQLReturnTable(sql);
                    foreach (DataRow xmdr in xmTable.Rows)
                    {
                        en.SetValByKey("NaShuiRenShiBieHao", xmdr["shb"]); //纳税人识别号
                        en.SetValByKey("KaiHuYinXing", xmdr["bankName"]);//开户银行
                        en.SetValByKey("YinXingZhangHao", xmdr["bankNum"]);//银行帐号
                        en.SetValByKey("KaiPiaoLianXiDiZhi", xmdr["addr"]); //开票联系地址
                        en.SetValByKey("KaiPiaoLianXiDianHua", xmdr["tel"]);//开票联系电话
                        en.SetValByKey("DianZiFaPiaoShouPiao", xmdr["email"]);//电子发票收票邮箱

                    }
                }
                if (rct > 0)
                {
                    en.Update();
                }
                else
                {
                    en.Insert();
                }
              
            }

            //详细发票信息
            DataTable dt1Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName, 1);
            string cls1 = "TS.PM.FaPiaoTZ";
            foreach (DataRow dr in dt1Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls1);
                string? ssNo = dr["开票编号"] as string; //开票编号
                if (existNo.Contains("," + ssNo + ","))
                {
                    continue;
                }
                en.SetValByKey("KpNo", ssNo);
                string? FaPiaoBianHao = dr["发票编号"] as string; // 发票编号
                en.SetValByKey("FaPiaoBianHao", FaPiaoBianHao);
                string? KaiPiaoRiQi = dr["开票日期"] as string; // 开票日期
                en.SetValByKey("KaiPiaoRiQi", KaiPiaoRiQi);
                string? ShuiLu = dr["税率"] as string; // 税率
                int slindex = queryIndex(ShuiLu,sl);
                en.SetValByKey("ShuiLu", slindex);
                string? ShuiE = dr["税额"] as string; // 税额
                en.SetValByKey("ShuiE",return0ByEmpt(ShuiE));
                string? FaPiaoJinE = dr["发票金额"] as string; // 发票金额
                en.SetValByKey("FaPiaoJinE", return0ByEmpt(FaPiaoJinE));
                string? FaPiaoBeiZhu = dr["发票备注"] as string; // 发票备注
                en.SetValByKey("FaPiaoBeiZhu", FaPiaoBeiZhu);
                //附件
                string? xh = dr["序号"] as string; //序号
                string rstr = upload_ath(zipFlodExt, "发票附件", xh + "-");
                if (!rstr.Equals(""))
                {
                    en.SetValByKey("FName", rstr.Split(";")[0]);
                    en.SetValByKey("FPath", rstr.Split(";")[1]);
                }
                en.Insert();
            }

            //结算资料
            DataTable dt2Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName, 2);
            string cls2 = "TS.PM.JieSuanFJ";
            foreach (DataRow dr in dt2Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls2);
                string? ssNo = dr["开票编号"] as string; //开票编号
                if (existNo.Contains("," + ssNo + ","))
                {
                    continue;
                }
                en.SetValByKey("KpNo", ssNo);
                string? xh = dr["序号"] as string; // 序号
                string rstr = upload_ath(zipFlodExt, "结算资料", xh + "-");
                if (!rstr.Equals(""))
                {
                    en.SetValByKey("FName", rstr.Split(";")[0]);
                    en.SetValByKey("FPath", rstr.Split(";")[1]);
                }
                en.Insert();
            }
            return msg;
        }
        
            //收入合同导入
            public string ZHTIncome_Import(string zipFlodExt)
        {
            string fullName = Get_ZipExcelFile(zipFlodExt);
            if (fullName == "")
            {
                return "err@文件不符合约定的格式，没有找到根目录下的文件.";
            }
            //主数据处理
            DataTable dt0Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName);
            string cls0 = "TS.PM.ZHTIncome";
            string msg = "";
            string existNo = ",";
            string[] sf = new string[] { "否", "是" };
            string[] xmlxs = new string[] { "房建", "市政", "燃气", "轨道", "敞口" };
            string[] status = new string[] { "履行中", "暂停", "终止" };
            string[] htly = new string[] { "委托", "投标", "其他" };
            string[] pslb = new string[] { "会签", "会议" };
            string[] yhtbgfs = new string[] { "增多", "减少" };
            string[] gzlzxlx = new string[] { "十万以下非轨道", "敞口", "十万以上非轨道" };
            string[] htsflx = new string[] { "定金", "进度款", "结算款" };
            string[] dydjd = new string[] { "中期成果", "最终成果" };
            string[] sl = new string[] { "0%", "1%", "3%", "6%" };
            DataTable danweis = DBAccess.RunSQLReturnTable("select * from PM_DanWei");
            DataTable zbbm = DBAccess.RunSQLReturnTable("SELECT No,Name, ParentNo FROM Port_Dept");
            foreach (DataRow dr in dt0Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls0);
                en.MyPK = dr[0].ToString();
               
                string? XiangMuBianHao = dr["项目编号"] as string; //项目编号
                if (XiangMuBianHao == null || XiangMuBianHao.Equals(""))
                {
                    msg += " err@编号:" + en.MyPK + "对应的项目编号不能为空";
                    continue;
                }
                int rct = en.RetrieveFromDBSources();
                en.SetValByKey("XiangMuBianHao", XiangMuBianHao);
                en.SetValByKey("HeTongMingChen", dr[1].ToString());
                string? XiangMuMingChen = dr["项目名称"] as string; //项目名称
                en.SetValByKey("XiangMuMingChen", XiangMuMingChen);
                string? XMLX = dr["项目类型"] as string; //项目类型
                int xmlxindex = queryIndex(XMLX,xmlxs);
                en.SetValByKey("XiangMuMingChen", xmlxindex);
                string? NiGaoRiQi = dr["拟稿日期"] as string; //拟稿日期
                en.SetValByKey("NiGaoRiQi", NiGaoRiQi);
                string? HeTongLaiYuan = dr["合同来源"] as string; //合同来源
                int htlyindex = queryIndex(HeTongLaiYuan, htly);
                en.SetValByKey("HeTongLaiYuan", htlyindex);
                string? QianDingRiQi = dr["签订日期"] as string; //签订日期
                en.SetValByKey("QianDingRiQi", QianDingRiQi);
                string? JianSheShanWeiT = dr["建设单位"] as string; //建设单位
                string JianSheShanWei = queryIndexByTable(JianSheShanWeiT, danweis);
                en.SetValByKey("JianSheShanWeiT", JianSheShanWeiT);
                en.SetValByKey("JianSheShanWei", JianSheShanWei);
                string? JSSW_Linrek = dr["建设单位联系人"] as string; //建设单位联系人
                en.SetValByKey("JSSW_Linrek", JSSW_Linrek);
                string? JSSW_Tel = dr["建设单位电话"] as string; //建设单位电话
                en.SetValByKey("JSSW_Tel", JSSW_Tel);
                string? JianSheGuanLiShanWeiT = dr["建设管理单位"] as string; //建设管理单位
                string JianSheGuanLiShanWei = queryIndexByTable(JianSheGuanLiShanWeiT, danweis);
                en.SetValByKey("JianSheGuanLiShanWeiT", JianSheGuanLiShanWeiT);
                en.SetValByKey("JianSheGuanLiShanWei", JianSheGuanLiShanWei);
                string? JSGLSW_Linrek = dr["建设管理单位联系人"] as string; //建设管理单位联系人
                en.SetValByKey("JSGLSW_Linrek", JSGLSW_Linrek);
                string? JSGLSW_Tel = dr["建设管理单位电话"] as string; //建设管理单位电话
                en.SetValByKey("JSGLSW_Tel", JSGLSW_Tel);
                string? ShiFouBuChongHeTong = dr["是否补充合同"] as string; //是否补充合同
                int sfbcindex = queryIndex(ShiFouBuChongHeTong, sf);
                en.SetValByKey("ShiFouBuChongHeTong", sfbcindex);
                string? ZhuHeTong = dr["主合同编号"] as string; //主合同编号
                en.SetValByKey("ZhuHeTong", ZhuHeTong);
                string? ZhuHeTongT = dr["主合同名称"] as string; //主合同名称
                en.SetValByKey("ZhuHeTongT", ZhuHeTongT);
                string? ZHTLJYKPJE = dr["主合同累计已开票金额"] as string; //主合同累计已开票金额
                en.SetValByKey("ZHTLJYKPJE", return0ByEmpt(ZHTLJYKPJE));
                string? ZHTLJYDZJE = dr["主合同累计已到帐金额"] as string; //主合同累计已到帐金额
                en.SetValByKey("ZHTLJYDZJE", return0ByEmpt(ZHTLJYDZJE));
                string? ZHTKGZJLJYQRGZL = dr["主合同开工至今累计已确认工作量"] as string; //主合同开工至今累计已确认工作量
                en.SetValByKey("ZHTKGZJLJYQRGZL", return0ByEmpt(ZHTKGZJLJYQRGZL));
                string? PingShenLeiBie = dr["评审类别"] as string; //评审类别
                int pslbindex = queryIndex(PingShenLeiBie, pslb);
                en.SetValByKey("PingShenLeiBie", pslbindex);
                string? HeTongJinE = dr["合同金额"] as string; //合同金额
                en.SetValByKey("HeTongJinE", return0ByEmpt(HeTongJinE));
                string? KCSZF = dr["勘察部分审查费"] as string; //勘察部分审查费
                en.SetValByKey("KCSZF", return0ByEmpt(KCSZF));
                string? SJSZF = dr["设计部分审查费"] as string; //设计部分审查费
                en.SetValByKey("SJSZF", return0ByEmpt(SJSZF));
                string? GongZuoLiangYingXian = dr["工作量影响金额"] as string; //工作量影响金额
                en.SetValByKey("GongZuoLiangYingXian", return0ByEmpt( GongZuoLiangYingXian));
                string? KanChaYingXiangJinE = dr["勘察影响金额"] as string; //勘察影响金额
                en.SetValByKey("KanChaYingXiangJinE", return0ByEmpt(KanChaYingXiangJinE));
                string? SheJiYingXiangJinE = dr["设计影响金额"] as string; //设计影响金额
                en.SetValByKey("SheJiYingXiangJinE", return0ByEmpt(SheJiYingXiangJinE));
                string? GCNRJXMYQGY = dr["工程内容及项目要求概要"] as string; //工程内容及项目要求概要
                en.SetValByKey("GCNRJXMYQGY", GCNRJXMYQGY);
                string? XiuDingBianGengNeiRo = dr["修订变更内容摘要"] as string; //修订变更内容摘要
                en.SetValByKey("XiuDingBianGengNeiRo", XiuDingBianGengNeiRo);
                string? SFYXZHTJE = dr["是否影响主合同金额"] as string; //是否影响主合同金额
                int sfyxindex = queryIndex(SFYXZHTJE, sf);
                en.SetValByKey("SFYXZHTJE", sfyxindex);
                string? XinZengBuChongJingE = dr["新增补充净额"] as string; //新增补充净额
                en.SetValByKey("XinZengBuChongJingE", return0ByEmpt(XinZengBuChongJingE));
                string? XianZhuHeTongJinE = dr["现主合同金额"] as string; //现主合同金额
                en.SetValByKey("XianZhuHeTongJinE", return0ByEmpt(XianZhuHeTongJinE));
                string ? StrZhuBanBuMenT = dr["主办部门"] as string; //主办部门
                string StrZhuBanBuMen = queryIndexByTable(StrZhuBanBuMenT,zbbm);
                en.SetValByKey("StrZhuBanBuMenT", StrZhuBanBuMenT);
                en.SetValByKey("StrZhuBanBuMen", StrZhuBanBuMen);
                string ? KeFenBaoYuSuanJinE = dr["可分包预算金额"] as string; //可分包预算金额
                en.SetValByKey("KeFenBaoYuSuanJinE", return0ByEmpt(KeFenBaoYuSuanJinE));
                string ? ShouRuHeTongLeiXing = dr["工作量子项类型"] as string; //工作量子项类型
                int zlindex = queryIndex(ShouRuHeTongLeiXing, gzlzxlx);
                en.SetValByKey("ShouRuHeTongLeiXing", zlindex);
                string ? YuanHeTongBianGengFa = dr["原合同变更方式"] as string; //原合同变更方式
                int bgindex = queryIndex(YuanHeTongBianGengFa, yhtbgfs);
                en.SetValByKey("YuanHeTongBianGengFa", bgindex);
                string ? HeTongYuanJinE = dr["合同原金额"] as string; //合同原金额
                en.SetValByKey("HeTongYuanJinE", return0ByEmpt(HeTongYuanJinE));
                string ? BianGengJinE = dr["变动金额"] as string; //变动金额
                en.SetValByKey("BianGengJinE", return0ByEmpt(BianGengJinE));
                string ? QueDingJinE = dr["变动后金额"] as string; //变动后金额
                en.SetValByKey("QueDingJinE", return0ByEmpt(QueDingJinE));
                string ? BianGengYuanYin = dr["变更原因"] as string; //变更原因
                en.SetValByKey("BianGengYuanYin", BianGengYuanYin);
                string ? SFXYLYBH = dr["是否需要履约保函"] as string; //是否需要履约保函
                int bhindex = queryIndex(SFXYLYBH, sf);
                en.SetValByKey("SFXYLYBH", bhindex);
                string ? QTFXTK = dr["其他风险条款"] as string; //其他风险条款
                en.SetValByKey("QTFXTK", QTFXTK);
                string ? BeiZhu = dr["备注"] as string; //备注
                en.SetValByKey("BeiZhu", BeiZhu);
                string ? HeTongShouFeiTiaoKua = dr["合同收费条款"] as string; //合同收费条款
                en.SetValByKey("HeTongShouFeiTiaoKua", HeTongShouFeiTiaoKua);
                string ? ShiFouLianHeTi = dr["是否联合体"] as string; //是否联合体
                int sfindex = queryIndex(ShiFouLianHeTi, sf);
                en.SetValByKey("ShiFouLianHeTi", sfindex);
                string ? LianHeTiFangJinE = dr["联合体方金额"] as string; //联合体方金额
                en.SetValByKey("LianHeTiFangJinE",return0ByEmpt(LianHeTiFangJinE));
                if (rct > 0)
                {
                    en.Update();
                }
                else
                {
                    en.Insert();
                }
                
            }

            //合同收费条款
            DataTable dt1Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName, 1);
            string cls1 = "TS.PM.ShouFeiTK";
            foreach (DataRow dr in dt1Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls1);
                string? ssNo = dr["合同编号"] as string; 
                if (existNo.Contains("," + ssNo + ","))
                {
                    continue;
                }
                en.SetValByKey("PrjNo", ssNo);
                string? HeTongShouFeiLeiXing = dr["类型"] as string; // 类型
                int srindex = queryIndex(HeTongShouFeiLeiXing, htsflx);
                en.SetValByKey("HeTongShouFeiLeiXing", srindex);
                string? DingJinRiQi = dr["定金日期"] as string; // 定金日期
                en.SetValByKey("DingJinRiQi", DingJinRiQi);
                string? JieDuanNeiRong = dr["阶段内容"] as string; // 阶段内容
                en.SetValByKey("JieDuanNeiRong", JieDuanNeiRong);
                string? DuiYingDaJieDuan = dr["对应大阶段"] as string; // 对应大阶段
                int djdindex = queryIndex(DuiYingDaJieDuan, dydjd);
                en.SetValByKey("DuiYingDaJieDuan", djdindex);
                string? JieDuanZhanBi = dr["阶段占比"] as string; //阶段占比
                en.SetValByKey("JieDuanZhanBi", JieDuanZhanBi);
                string? YuQiShouRu = dr["预期收入"] as string; //预期收入
                en.SetValByKey("YuQiShouRu", YuQiShouRu);

                en.Insert();
            }

            //联合体
            DataTable dt2Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName, 2);
            string cls2 = "TS.PM.LianHeTi";
            foreach (DataRow dr in dt2Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls2);
                string? ssNo = dr["合同编号"] as string; //合同编号
                if (existNo.Contains("," + ssNo + ","))
                {
                    continue;
                }
                en.SetValByKey("PrjNo", ssNo);
                string? GongSiMingT = dr["公司名"] as string; // 公司名
                string GongSiMing = queryIndexByTable(GongSiMingT, danweis);
                en.SetValByKey("GongSiMingT", GongSiMingT);
                en.SetValByKey("GongSiMing", GongSiMing);
                string? QianTouFang = dr["牵头方"] as string; // 牵头方
                int qtdindex = queryIndex(QianTouFang, sf);
                en.SetValByKey("QianTouFang", qtdindex);
                string? LianHeTiJinE = dr["联合体金额"] as string; // 联合体金额
                en.SetValByKey("LianHeTiJinE", return0ByEmpt(LianHeTiJinE));
                string? WoSiDaiShouJinE = dr["我司代收金额"] as string; // 我司代收金额
                en.SetValByKey("WoSiDaiShouJinE", WoSiDaiShouJinE);
                string? ShuiLu = dr["税率"] as string; // 税率
                int slindex = queryIndex(ShuiLu, sl);
                en.SetValByKey("ShuiLu", slindex);
                string? BeiZhu = dr["备注"] as string; // 备注
                en.SetValByKey("BeiZhu", BeiZhu);
                string? LianHeTiZhuangTai = dr["状态"] as string; // 状态
                int ztindex = queryIndex(LianHeTiZhuangTai, status);
                en.SetValByKey("LianHeTiZhuangTai", ztindex);

                en.Insert();
            }

            //收入子项
            DataTable dt3Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName, 3);
            string cls3 = "TS.PM.ShouRuZXCF";
            foreach (DataRow dr in dt3Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls3);
                string? ssNo = dr["合同编号"] as string; //合同编号
                if (existNo.Contains("," + ssNo + ","))
                {
                    continue;
                }
                en.SetValByKey("PrjNo", ssNo);
                string? ShouRuZiXiangMu = dr["收入子项目"] as string; // 收入子项目
                string? JinE = dr["金额"] as string; // 金额
                en.SetValByKey("ShouRuZiXiangMu", return0ByEmpt(ShouRuZiXiangMu));
                en.SetValByKey("JinE", JinE);

                en.Insert();
            }
            //扫描件
            DataTable dt4Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName,4);
            string cls4= "TS.PM.SaoMiaoFJ";
            foreach (DataRow dr in dt4Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls4);
                string? ssNo = dr["合同编号"] as string; //合同编号
                if (existNo.Contains("," + ssNo + ","))
                {
                    continue;
                }
                en.SetValByKey("PrjNo", ssNo);
                string? xh = dr["序号"] as string; // 序号
                string rstr = upload_ath(zipFlodExt, "上传扫描附件", xh + "-");
                if (!rstr.Equals(""))
                {
                    en.SetValByKey("FName", rstr.Split(";")[0]);
                    en.SetValByKey("FPath", rstr.Split(";")[1]);
                }
                en.Insert();
            }
            //中标附件
            DataTable dt5Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName, 5);
            string cls5 = "TS.PM.ZhongBiaoFJ";
            foreach (DataRow dr in dt5Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls5);
                string? ssNo = dr["合同编号"] as string; //合同编号
                if (existNo.Contains("," + ssNo + ","))
                {
                    continue;
                }
                en.SetValByKey("PrjNo", ssNo);
                string? xh = dr["序号"] as string; // 序号
                string rstr = upload_ath(zipFlodExt, "上传扫描附件", xh + "-");
                if (!rstr.Equals(""))
                {
                    en.SetValByKey("FName", rstr.Split(";")[0]);
                    en.SetValByKey("FPath", rstr.Split(";")[1]);
                }
                en.Insert();
            }

            return msg;
        }
        //项目送审处理
        public string PrjSSLC_Import(string zipFlodExt)
        {
            string fullName= Get_ZipExcelFile(zipFlodExt);
            if (fullName == "")
            {
                return "err@文件不符合约定的格式，没有找到根目录下的文件.";
            }
            //主数据处理
            DataTable dt0Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName);
            string cls0 = "TS.PM.PrjSSLC";
            string msg = "";
            string existNo = ",";
            string[] htlx = new string[] { "市政", "道路", "燃气", "建筑" };
            string[] jsyj = new string[] { "长度", "工程投资额", "面积", "其它" };
            string[] qtlx = new string[] { "涉及工程质量安全", "涉及消防安全" };
            DataTable zhuanyes = DBAccess.RunSQLReturnTable("select * from pm_ZhuanYe");
            DataTable jieduans = DBAccess.RunSQLReturnTable("select * from PM_WorkJieDuan");
            DataTable gongzuos = DBAccess.RunSQLReturnTable("select * from PM_WorkNode");
            foreach (DataRow dr in dt0Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls0);
                en.MyPK = dr[0].ToString();
                
               
                string? PrjNo = dr["项目编号"] as string; //项目编号
                if(PrjNo==null || PrjNo.Equals(""))
                {
                    msg += " err@编号:" + en.MyPK + "对应的项目编号不能为空" ;
                    continue;
                }
                int rct = en.RetrieveFromDBSources();
                en.SetValByKey("PrjNo", PrjNo);
                string? PrjName = dr["项目名称"] as string; //项目名称
                en.SetValByKey("PrjName", PrjName);
                string? HeTongLeiXing = dr["合同类型"] as string; //合同类型
                int htlxindex = queryIndex(HeTongLeiXing, htlx);
                en.SetValByKey("HeTongLeiXing", htlxindex);
                string? GZLJSYJ = dr["工作量计算依据"] as string; //工作量计算依据
                int jsyjindex = queryIndex(GZLJSYJ, jsyj);
                en.SetValByKey("GZLJSYJ", jsyjindex);
                string? SheJiHeTongJinE = dr["设计合同金额"] as string; //设计合同金额
                en.SetValByKey("SheJiHeTongJinE", SheJiHeTongJinE);
                string? SheJiZhanBi = dr["设计占比"] as string; //设计占比
                en.SetValByKey("SheJiZhanBi", SheJiZhanBi);
                string? SJWCZCDZGCTZEZMJQT = dr["设计完成总量"] as string; //设计完成总量
                en.SetValByKey("SJWCZCDZGCTZEZMJQT", SJWCZCDZGCTZEZMJQT);
                string? KanChaHeTongJinE = dr["勘察合同金额"] as string; //勘察合同金额
                en.SetValByKey("KanChaHeTongJinE", KanChaHeTongJinE);
                string? KanChaZhanBi = dr["勘察占比"] as string; //勘察占比
                en.SetValByKey("KanChaZhanBi", KanChaZhanBi);
                string? KanChaWanChengZongCh = dr["勘察完成总量"] as string; //勘察完成总量
                en.SetValByKey("KanChaWanChengZongCh", KanChaWanChengZongCh);
                string? JiHuaWanChengShiJian = dr["计划完成时间"] as string; //计划完成时间
                en.SetValByKey("JiHuaWanChengShiJian", JiHuaWanChengShiJian);
                string sql = "select SFZXM,ZXMBH,ZXMBHT,LiXiangShiJian,JSDWMC,JSDWMCT,JSDW_Linker,JSDW_Tel" +
                    ",DJGLDW,DJGLDWT,DJGLDW_Linker,DJGLDW_Tel,ZCBDW,ZCBDWT,ZCBDW_Linker,ZCBDW_Tel,SJDWMC,SJDWMCT,SJDW_Linker" +
                    ",SJDW_Tel,KCSWMC,KCSWMCT,KCDW_Linker,KCDW_Tel,GongChengGaiKuang,XMLX,HeTongLeiXing,GZLJSYJ,SheJiHeTongJinE" +
                    ",SheJiZhanBi,SJWCZCDZGCTZEZMJQT,KanChaHeTongJinE,KanChaZhanBi,KanChaWanChengZongCh" +
                    ",FuZeRen,FuZeRenT,XiangMuFuZeRen,XiangMuFuZeRenT   from PM_Prj where No='" + PrjNo + "'";
                DataTable xmTable = DBAccess.RunSQLReturnTable(sql);
                foreach (DataRow xmdr in xmTable.Rows)
                {
                    en.SetValByKey("SFZXM", xmdr["SFZXM"]);
                    en.SetValByKey("ZXMBH", xmdr["ZXMBH"]);
                    en.SetValByKey("ZXMBHT", xmdr["ZXMBHT"]);
                    en.SetValByKey("LiXiangShiJian", xmdr["LiXiangShiJian"]);
                    en.SetValByKey("JSDWMC", xmdr["JSDWMC"]);
                    en.SetValByKey("JSDWMCT", xmdr["JSDWMCT"]);
                    en.SetValByKey("JSDW_Linker", xmdr["JSDW_Linker"]);
                    en.SetValByKey("JSDW_Tel", xmdr["JSDW_Tel"]);
                    en.SetValByKey("DJGLDW", xmdr["DJGLDW"]);
                    en.SetValByKey("DJGLDWT", xmdr["DJGLDWT"]);
                    en.SetValByKey("DJGLDW_Linker", xmdr["DJGLDW_Linker"]);
                    en.SetValByKey("DJGLDW_Tel", xmdr["DJGLDW_Tel"]);
                    en.SetValByKey("ZCBDW", xmdr["ZCBDW"]);
                    en.SetValByKey("ZCBDWT", xmdr["ZCBDWT"]);
                    en.SetValByKey("ZCBDW_Linker", xmdr["ZCBDW_Linker"]);
                    en.SetValByKey("ZCBDW_Tel", xmdr["ZCBDW_Tel"]);
                    en.SetValByKey("SJDWMC", xmdr["SJDWMC"]);
                    en.SetValByKey("SJDWMCT", xmdr["SJDWMCT"]);
                    en.SetValByKey("SJDW_Linker", xmdr["SJDW_Linker"]);
                    en.SetValByKey("SJDW_Tel", xmdr["SJDW_Tel"]);
                    en.SetValByKey("KCSWMC", xmdr["KCSWMC"]);
                    en.SetValByKey("KCSWMCT", xmdr["KCSWMCT"]);
                    en.SetValByKey("KCDW_Linker", xmdr["KCDW_Linker"]);
                    en.SetValByKey("KCDW_Tel", xmdr["KCDW_Tel"]);
                    en.SetValByKey("XMLX", xmdr["XMLX"]);
                    en.SetValByKey("HeTongLeiXing", xmdr["HeTongLeiXing"]);
                    en.SetValByKey("GZLJSYJ", xmdr["GZLJSYJ"]);
                    en.SetValByKey("SheJiHeTongJinE", xmdr["SheJiHeTongJinE"]);
                    en.SetValByKey("SheJiZhanBi", xmdr["SheJiZhanBi"]);
                    en.SetValByKey("SJWCZCDZGCTZEZMJQT", xmdr["SJWCZCDZGCTZEZMJQT"]);
                    en.SetValByKey("KanChaHeTongJinE", xmdr["KanChaHeTongJinE"]);
                    en.SetValByKey("KanChaZhanBi", xmdr["KanChaZhanBi"]);
                    en.SetValByKey("KanChaWanChengZongCh", xmdr["KanChaWanChengZongCh"]);

                    en.SetValByKey("FuZeRen", xmdr["FuZeRen"]);
                    en.SetValByKey("FuZeRenT", xmdr["FuZeRenT"]);
                    en.SetValByKey("XiangMuFuZeRen", xmdr["XiangMuFuZeRen"]);
                    en.SetValByKey("XiangMuFuZeRenT", xmdr["XiangMuFuZeRenT"]);
                }
                if (rct > 0)
                {
                    en.Update();
                }
                else
                {
                    en.Insert();
                }
             
            }

            //图纸处理
            DataTable dt1Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName,1);
            string cls1 = "TS.PM.TuoZhi";
            foreach (DataRow dr in dt1Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls1);
                string? ssNo = dr["送审流水号"] as string; //送审流水号
                if (existNo.Contains(","+ ssNo + ","))
                {
                    continue;
                }
                en.SetValByKey("PrjNo", ssNo);
                string? ZhuanYeT = dr["专业"] as string; // 专业
                string ZhuanYe = queryIndexByTable(ZhuanYeT, zhuanyes);
                en.SetValByKey("ZhuanYeT", ZhuanYeT);
                en.SetValByKey("ZhuanYe", ZhuanYe);

                string? RenYuanT = dr["审查人员"] as string; //审查人员
                string? RenYuan = dr["审查人员账号"] as string; //审查人员账号
                en.SetValByKey("RenYuanT", RenYuanT);
                en.SetValByKey("RenYuan", RenYuan);
                string? TuZhiShuLiang = dr["图纸数量"] as string; //图纸数量
                en.SetValByKey("TuZhiShuLiang", TuZhiShuLiang);
                string? XiShu = dr["系数"] as string; //系数
                en.SetValByKey("XiShu", XiShu);

                //附件
                string? xh = dr["序号"] as string; //序号
                string? TzName = dr["图纸名称"] as string; //图纸名称
                string rstr = upload_ath(zipFlodExt, "图纸", xh+"-1");
                if (!rstr.Equals(""))
                {
                    en.SetValByKey("TzName", rstr.Split(";")[0]);
                    en.SetValByKey("TzPath", rstr.Split(";")[1]);
                }
              
                string? CsName = dr["初审意见附件"] as string; //初审意见附件
                rstr = upload_ath(zipFlodExt, "图纸", xh + "-2");
                if (!rstr.Equals(""))
                {
                    en.SetValByKey("CsName", rstr.Split(";")[0]);
                    en.SetValByKey("CsPath", rstr.Split(";")[1]);
                }
                string? QrName = dr["已确认意见附件"] as string; //已确认意见附件
                rstr = upload_ath(zipFlodExt, "图纸", xh + "-3");
                if (!rstr.Equals(""))
                {
                    en.SetValByKey("QrName", rstr.Split(";")[0]);
                    en.SetValByKey("QrPath", rstr.Split(";")[1]);
                }
                en.Insert();
            }

            //轨道工作内容
            DataTable dt2Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName, 2);
            string cls2 = "TS.PM.WorkDtlSS";
            foreach (DataRow dr in dt2Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls2);
                string? ssNo = dr["送审流水号"] as string; //送审流水号
                if (existNo.Contains("," + ssNo + ","))
                {
                    continue;
                }
                en.SetValByKey("PrjNo", ssNo);
                string? JieDuanNoT = dr["阶段名称"] as string; // 阶段名称
                string JieDuanNo = queryIndexByTable(JieDuanNoT, jieduans);
                en.SetValByKey("JieDuanNoT", JieDuanNoT);
                en.SetValByKey("JieDuanNo", JieDuanNo);
                string? WorkNodeNoT = dr["工作节点"] as string; // 工作节点
                string WorkNodeNo = queryIndexByTable(WorkNodeNoT, gongzuos);
                en.SetValByKey("WorkNodeNoT", WorkNodeNoT);
                en.SetValByKey("WorkNodeNo", WorkNodeNo);
                string? BiLv = dr["进度比率"] as string; // 进度比率
                en.SetValByKey("BiLv", BiLv);
                string? NumOfAll = dr["总量"] as string; // 总量
                en.SetValByKey("NumOfAll", NumOfAll);
                string? BenCiSongShenLiang = dr["本次送审量"] as string; // 本次送审量
                en.SetValByKey("BenCiSongShenLiang", BenCiSongShenLiang);
                en.Insert();
            }

            //强条统计内容
            DataTable dt3Rows = BP.DA.DBLoad.ReadExcelFileToDataTable(fullName, 3);
            string cls3 = "TS.PM.QiangTiaoSS";
            foreach (DataRow dr in dt3Rows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls3);
                string? ssNo = dr["送审流水号"] as string; //送审流水号
                if (existNo.Contains("," + ssNo + ","))
                {
                    continue;
                }
                en.SetValByKey("PrjNo", ssNo);
                string? YueFen = dr["月份"] as string; // 月份
                en.SetValByKey("YueFen", YueFen);
                string? SQL_ZhuanYeT = dr["专业"] as string; // 专业
                string SQL_ZhuanYe = queryIndexByTable(SQL_ZhuanYeT, zhuanyes);
                en.SetValByKey("SQL_ZhuanYeT", SQL_ZhuanYeT);
                en.SetValByKey("SQL_ZhuanYe", SQL_ZhuanYe);
                string? GongChengMingChen = dr["工程名称"] as string; // 工程名称
                en.SetValByKey("GongChengMingChen", GongChengMingChen);
                string? QiangTiaoLeiXing = dr["强条类型"] as string; // 强条类型
                int qtlxindex = queryIndex(QiangTiaoLeiXing, qtlx);
                en.SetValByKey("QiangTiaoLeiXing", qtlxindex);
                string? QiangTiaoShuLiang = dr["强条数量"] as string; // 强条数量
                en.SetValByKey("QiangTiaoShuLiang", QiangTiaoShuLiang);
                string? SuoSheJiQiangTiaoYiJ = dr["所涉及强条意见"] as string; // 所涉及强条意见
                en.SetValByKey("SuoSheJiQiangTiaoYiJ", SuoSheJiQiangTiaoYiJ);

                en.Insert();
            }
            return msg;
        }
            
        //项目立项导入
        public string Prj_Import(string filePath)
        {
            DataTable dtRows = BP.DA.DBLoad.ReadExcelFileToDataTable(filePath);
            string cls = "TS.PM.Prj";
            string msg = "";
            string[] xmlxs = new string[] { "房建", "市政", "燃气", "轨道" };
            string[] zxmlxs = new string[] { "非轨道", "轨道类", "敞口" };
            string[] srzxmlx = new string[] { "十万以下非轨道", "轨道类", "敞口" , "十万以上非轨道" };
            string[] status = new string[] { "履行中", "暂停" };
            string[] htdd = new string[] { "市内", "省内", "省外", "国外" };
            DataTable zhuanyes = DBAccess.RunSQLReturnTable("select * from pm_ZhuanYe");
            DataTable danweis = DBAccess.RunSQLReturnTable("select * from PM_DanWei");
            DataTable sheng = DBAccess.RunSQLReturnTable("select * from sys_area where type=1");
            DataTable shi = DBAccess.RunSQLReturnTable("select * from sys_area where type=2");
            DataTable emp = DBAccess.RunSQLReturnTable("select no,name from port_emp");
            foreach (DataRow dr in dtRows.Rows)
            {
                TSEntityMyPK en = new TSEntityMyPK(cls);
                string? No = dr["项目编号"] as string; //项目编号
                if(No==null || No.Equals(""))
                {
                    continue;
                }
                en.MyPK = No;
                int rct = en.RetrieveFromDBSources();
                string? PrjName = dr["项目名称"] as string; //项目名称
                en.SetValByKey("PrjName", PrjName);
                en.SetValByKey("Name", PrjName);
               
                //string? XMLX = dr["项目类型"] as string; //项目类型
                //int xmlxindex = queryIndex(XMLX, xmlxs);
                //en.SetValByKey("XMLX", xmlxindex);
                //string? ShouRuZiXiangMuLeiXi = dr["收入子项目类型"] as string; //收入子项目类型
                //int zxmlxindex = queryIndex(ShouRuZiXiangMuLeiXi, srzxmlx);
                //en.SetValByKey("ShouRuZiXiangMuLeiXi", zxmlxindex);

                // string? lixiangshijian = dr["立项时间"] as string; //立项时间
                // en.setvalbykey("lixiangshijian", lixiangshijian);
                // string? sfzxm = dr["是否子项目"] as string; //是否子项目
                // sfzxm = isnotrant(sfzxm);
                // en.setvalbykey("sfzxm", sfzxm);
                // string? sql_zhuanyet = dr["主专业"] as string; //专业
                // string sql_zhuanye = queryindexbytable(sql_zhuanyet, zhuanyes);
                // en.setvalbykey("sql_zhuanye", sql_zhuanye);
                // en.setvalbykey("sql_zhuanyet", sql_zhuanyet);
                // //string? zxmbh = dr["主项目编号"] as string; //主项目编号
                //// en.setvalbykey("zxmbh", zxmbh);
                // string? jsdwmct = dr["建设单位名称"] as string; //建设单位
                // string jsdwmc = queryindexbytable(jsdwmct, danweis);
                // en.setvalbykey("jsdwmct", jsdwmct);
                // en.setvalbykey("jsdwmc", jsdwmc);
                // string? jsdw_linker = dr["建设单位联系人"] as string; //联系人
                // en.setvalbykey("jsdw_linker", jsdw_linker);
                // string? jsdw_tel = dr["建设单位电话"] as string; //电话
                // en.setvalbykey("jsdw_tel", jsdw_tel);
                // en.setvalbykey("shouruzixiangmuleixi", shouruzixiangmuleixi);
                // string? prjsta = dr["状态"] as string; //状态
                //  int staindex = queryindex(prjsta, status);
                // en.setvalbykey("prjsta", staindex);
                // string? djgldwt = dr["代建管理单位名称"] as string; //代建管理单位名称
                // string djgldw = queryindexbytable(djgldwt, danweis);
                // en.setvalbykey("djgldwt", djgldwt);
                // en.setvalbykey("djgldw", djgldw);
                // string? djgldw_linker = dr["代建管理单位联系人"] as string; //联系人
                // en.setvalbykey("djgldw_linker", djgldw_linker);
                // string? djgldw_tel = dr["代建管理单位电话"] as string; //电话
                // en.setvalbykey("djgldw_tel", djgldw_tel);
                // string? zcbdwt = dr["总承包单位名称"] as string; //总承包单位名称
                // string zcbdw = queryindexbytable(zcbdwt, danweis);
                // en.setvalbykey("zcbdwt", zcbdwt);
                // en.setvalbykey("zcbdw", zcbdw);
                // string? zcbdw_linker = dr["总承包单位联系人"] as string; //联系人
                // en.setvalbykey("zcbdw_linker", zcbdw_linker);
                // string? zcbdw_tel = dr["总承包单位电话"] as string; //电话
                // en.setvalbykey("zcbdw_tel", zcbdw_tel);
                // string? sjdwmct = dr["设计单位名称"] as string; //设计单位
                // string sjdwmc = queryindexbytable(sjdwmct, danweis);
                // en.setvalbykey("sjdwmct", sjdwmct);
                // en.setvalbykey("sjdwmc", sjdwmc);
                // string? sjdw_linker = dr["设计单位联系人"] as string; //联系人
                // en.setvalbykey("sjdw_linker", sjdw_linker);
                // string? sjdw_tel = dr["设计单位电话"] as string; //电话
                // en.setvalbykey("sjdw_tel", sjdw_tel);
                // string? kcswmct = dr["勘察单位名称"] as string; //勘察单位
                // string kcswmc = queryindexbytable(kcswmct, danweis);
                // en.setvalbykey("kcswmct", kcswmct);
                // en.setvalbykey("kcswmc", kcswmc);
                // string? kcdw_linker = dr["勘察单位联系人"] as string; //联系人
                // en.setvalbykey("kcdw_linker", kcdw_linker);
                // string? kcdw_tel = dr["勘察单位电话"] as string; //电话
                // en.setvalbykey("kcdw_tel", kcdw_tel);
                // string? hetongdidian = dr["合同地点"] as string; //合同地点
                // int htddindex = queryindex(hetongdidian, htdd);
                // en.setvalbykey("hetongdidian", htddindex);
                // string? sql_shengt = dr["省"] as string; //省
                // string sql_sheng = queryindexbytable(sql_shengt, sheng);
                // en.setvalbykey("sql_shengt", sql_shengt);
                // en.setvalbykey("sql_sheng", sql_sheng);

                // string? sql_shit = dr["市"] as string; //市
                // string sql_shi = queryindexbytable(sql_shit, shi);
                // en.setvalbykey("sql_shit", sql_shit);
                // en.setvalbykey("sql_shi", sql_shi);

                // string? gongchenggaikuang = dr["工程概况"] as string; //工程概况
                // en.setvalbykey("gongchenggaikuang", gongchenggaikuang);
                // string? fuzerent = dr["服务人员"] as string; //服务人员
                // string? fuzeren = dr["服务人员登录账号"] as string; //服务人员登录账号
                // en.setvalbykey("fuzerent", fuzerent);
                // if (fuzeren==null || fuzeren.equals(""))
                // {
                //     fuzeren = queryindexbytable(fuzerent, emp);
                // }
                // en.setvalbykey("fuzeren", fuzeren);
                // string ? xiangmufuzerent = dr["项目负责人"] as string; //项目负责人
                // string ? xiangmufuzeren = dr["项目负责人登录账号"] as string; //项目负责人登录账号
                // en.setvalbykey("xiangmufuzerent", xiangmufuzerent);
                //  if (xiangmufuzeren == null || xiangmufuzeren.equals(""))
                // {
                //     xiangmufuzeren = queryindexbytable(xiangmufuzerent, emp);
                // }
                // en.setvalbykey("xiangmufuzeren", xiangmufuzeren);

              
                if (rct > 0)
                {
                    en.Update();
                }
                else
                {
                    en.Insert();
                }


              
            }
            return msg;
        }
        public string isNoTrant(string info)
        {
          if(info==null || info.Equals("") || info.Equals("否"))
            {
                return "0";
            }
            else
            {
                return "1";
            }
        }

        public int queryIndex(string str, string[] arr)
        {
            if(str==null || str.Equals(""))
            {
                return 0;
            }

            for (int i = 0; i < arr.Length; i++)
            {
                if (str.Equals(arr[i]))
                {
                    return i;
                }
            }
             return 0;
        }

        public string queryIndexByTable(string str, DataTable dataTable)
        {
            if (str == null || str.Equals(""))
            {
                return "0";
            }
            foreach (DataRow row in dataTable.Rows)
            {
                string name = row["Name"].ToString();
                string no = row["No"].ToString();
                if (str.Equals(name))
                {
                    return no;
                }
            }
               
            return "0";
        }
        public string queryIndexByTableAndOther(string str,string filedName, string fieldValue, DataTable dataTable)
        {
            if (str == null || str.Equals(""))
            {
                return "0";
            }
            foreach (DataRow row in dataTable.Rows)
            {
                string name = row["Name"].ToString();
                string no = row["No"].ToString();
                string filedv = row[filedName].ToString();
                if (str.Equals(name) && filedv.Equals(fieldValue))
                {
                    return no;
                }
            }

            return "0";
        }

        //导入数据类型检测
        public   string PM_ImportTypeCheck()
        {
            string msg = "";
            IFormFile file = HttpContextHelper.RequestFiles(0);
            if (file == null)
            {
                return "err@没有获取到文件";
            }
            string type = this.GetRequestVal("type");
            string impType = this.GetRequestVal("impType");
            string fileName = System.IO.Path.GetFileName(file.FileName);
            //xls xlsx格式验证
            if (impType.Equals("0"))
            {
                if (!fileName.EndsWith(".xlsx"))
                {
                    return "err@必须是.xlsx文件";
                }
            }
            else  //zip格式验证
            {
                if (!fileName.EndsWith(".zip"))
                {
                    return "err@必须是.zip文件";
                }
            }

            return msg;
        }
        public string Get_ZipExcelFile(string zipFlodExt)
        {
            System.IO.DirectoryInfo folder = new System.IO.DirectoryInfo(zipFlodExt);
            //文件夹存在
            if (folder.Exists)
            {
                System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(zipFlodExt);
                FileInfo[] fls = dir.GetFiles();
                DirectoryInfo[] dirs = dir.GetDirectories();
                foreach (FileInfo file in fls)
                {
                   if(file.FullName.EndsWith(".xlsx"))
                    {
                        return file.FullName;
                    }
                }
            }
            return "";
        }
        /// <summary>
        /// 获取文件夹中文件
        /// </summary>
        /// <param name="zipFlodExt">文件夹所在目录</param>
        /// <param name="name">文件夹中的文件夹名称</param>
        /// <param name="xh">所需附件序号</param>
        /// <returns>图片名称,图片名称;存储路径,存储路径</returns>
        public string upload_ath(string zipFlodExt, string name, string xh)
        {
            System.IO.DirectoryInfo folder = new System.IO.DirectoryInfo(zipFlodExt);
            //文件夹存在
            if (folder.Exists)
            {
                System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(zipFlodExt);
                FileInfo[] fls = dir.GetFiles();
                DirectoryInfo[] dirs = dir.GetDirectories();
                foreach (DirectoryInfo d in dirs)
                {
                    if (d.Name.Equals(name))
                    {
                        System.IO.DirectoryInfo dc = new System.IO.DirectoryInfo(d.FullName);
                        FileInfo[] flsc = dc.GetFiles();
                        string rName = "";
                        string rPath = "";
                        foreach (FileInfo fc in flsc)
                        {
                            if (fc.Name.StartsWith(xh))
                            {
                                if (rName.Equals(""))
                                {
                                    rName += fc.Name;
                                    rPath += fc.FullName;
                                }
                                else
                                {
                                    rName += ","+fc.Name;
                                    rPath += "," + fc.FullName;
                                }
                            }
                        }
                        return rName + ";" + rPath;
                    }
                }
            }
            return "";
        }
        /// <summary>
        /// 历史合同附件的处理,插入扫描件表
        /// </summary>
        /// <param name="mypk">合同编号</param>
        /// <param name="htYear">合同的年份</param>
        /// <param name="htXh">合同的序号</param>
        /// <returns>图片名称,图片名称;存储路径,存储路径</returns>
        public string move_ath(string mypk)
        {
            string spath = "D:\\dsm\\客户文档\\广州市政\\数据\\原始历史数据\\nowHT";
            string opath = "/mnt/install/ccflowNetCore/Publish/DataUser/UploadFile/ht";
            System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(spath);
            if (!mypk.Contains("-"))
            {
                return "mypk格式错误";
            }
            //合同的年份
            string htYear = mypk.Split("-")[0];
            //合同的序号
            string htXh = mypk.Split("-")[1];
            //文件夹存在
            if (dir.Exists)
            {
                FileInfo[] fls = dir.GetFiles();
                DirectoryInfo[] dirs = dir.GetDirectories();
                foreach (DirectoryInfo d in dirs)
                {
                    if (d.Name.Contains(htYear))
                    {
                        System.IO.DirectoryInfo dc = new System.IO.DirectoryInfo(d.FullName);
                        FileInfo[] flsc = dc.GetFiles();
                        DirectoryInfo[] dirsc = dc.GetDirectories();
                        foreach (FileInfo fc in flsc)
                        {
                            if (fc.Name.Contains(htYear) && fc.Name.Contains(htXh))
                            {
                                string sql = "insert into PM_SaoMiaoFJ (MyPK,PrjNo,FName,FPath,RDT) values(" + SystemConfig.AppCenterDBVarStr + "v1," + SystemConfig.AppCenterDBVarStr + "v2," + SystemConfig.AppCenterDBVarStr + "v3," + SystemConfig.AppCenterDBVarStr + "v4," + SystemConfig.AppCenterDBVarStr + "v5)";
                                string fpath = fc.FullName.Replace(spath, opath);
                                fpath = fpath.Replace("\\", "/");
                                string date = DateTime.Now.ToString("yyyy-MM-dd");
                                Paras ps = new Paras();
                                ps.SQL = sql;
                                ps.Add("v1", DBAccess.GenerGUID());
                                ps.Add("v2", mypk);
                                ps.Add("v3", fc.Name);
                                ps.Add("v4", fpath);
                                ps.Add("v5", date);
                                DBAccess.RunSQL(ps);
                            }
                        }
                        foreach (DirectoryInfo dcs in dirsc)
                        {
                            if (dcs.Name.Contains(htYear) && dcs.Name.Contains(htXh))
                            {
                                System.IO.DirectoryInfo dcc = new System.IO.DirectoryInfo(dcs.FullName);
                                FileInfo[] flscc = dcc.GetFiles();
                                foreach (FileInfo fc in flscc)
                                {
                                    if (fc.Name.Contains(htYear) && fc.Name.Contains(htXh))
                                    {
                                        string sql = "insert into PM_SaoMiaoFJ (MyPK,PrjNo,FName,FPath,RDT) values(" + SystemConfig.AppCenterDBVarStr + "v1," + SystemConfig.AppCenterDBVarStr + "v2," + SystemConfig.AppCenterDBVarStr + "v3," + SystemConfig.AppCenterDBVarStr + "v4," + SystemConfig.AppCenterDBVarStr + "v5)";
                                        string fpath = fc.FullName.Replace(spath, opath);
                                        fpath = fpath.Replace("\\", "/");
                                        string date = DateTime.Now.ToString("yyyy-MM-dd");
                                        Paras ps = new Paras();
                                        ps.SQL = sql;
                                        ps.Add("v1", DBAccess.GenerGUID());
                                        ps.Add("v2", mypk);
                                        ps.Add("v3", fc.Name);
                                        ps.Add("v4", fpath);
                                        ps.Add("v5", date);
                                        DBAccess.RunSQL(ps);
                                    }
                                }
                            }
                        }
                    }
                }

            }
            return "成功";
        }
        /// <summary>
        /// 合同附件的处理
        /// </summary>
        /// <param name="htYear">合同的年份</param>
        /// <param name="htXh">合同的序号</param>
        /// <returns>图片名称,图片名称;存储路径,存储路径</returns>
        public string move_ath(string htYear, string htXh)
        {
            string spath = "C:\\Users\\test\\Desktop\\审图历史数据\\HT";
            string tpath = "C:\\Users\\test\\Desktop\\审图历史数据\\nowHT";
            System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(spath);
            //文件夹存在
            if (dir.Exists)
            {
                FileInfo[] fls = dir.GetFiles();
                DirectoryInfo[] dirs = dir.GetDirectories();
                foreach (DirectoryInfo d in dirs)
                {
                    if (d.Name.Contains(htYear))
                    {
                        System.IO.DirectoryInfo dc = new System.IO.DirectoryInfo(d.FullName);
                        FileInfo[] flsc = dc.GetFiles();
                        DirectoryInfo[] dirsc = dc.GetDirectories();
                        foreach (FileInfo fc in flsc)
                        {
                            if (fc.Name.Contains(htYear) && fc.Name.Contains(htXh))
                            {
                                //附件迁移
                                copyFile(fc.FullName, fc.FullName.Replace("HT", "nowHT"));
                            }
                        }
                        foreach (DirectoryInfo dcs in dirsc)
                        {
                            if (dcs.Name.Contains(htYear) && dcs.Name.Contains(htXh))
                            {
                                System.IO.DirectoryInfo dcc = new System.IO.DirectoryInfo(dcs.FullName);
                                FileInfo[] flscc = dcc.GetFiles();
                                foreach (FileInfo fc in flscc)
                                {
                                    if (fc.Name.Contains(htYear) && fc.Name.Contains(htXh))
                                    {
                                        //附件迁移
                                        copyFile(fc.FullName, fc.FullName.Replace("HT", "nowHT"));
                                    }
                                }
                            }
                        }
                    }
                }

            }
            return "";
        }
        void copyFile(string sourceFile, string destinationFile)
        {
           
            //string sourceFile = @"C:\source\myfile.txt";
            //string destinationFile = @"C:\destination\myfile.txt";

            try
            {
                bool fileExists = File.Exists(destinationFile);
                if (fileExists)
                {
                    return;
                }
                // 确保目标路径存在
                string destinationDirectory = Path.GetDirectoryName(destinationFile);
                if (!Directory.Exists(destinationDirectory))
                {
                    Directory.CreateDirectory(destinationDirectory);
                }

                // 复制文件
                File.Copy(sourceFile, destinationFile, true); // true 表示如果目标文件存在，则覆盖它

            }
            catch (IOException ioEx)
            {
                Console.WriteLine("发生IO错误: " + ioEx.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine("发生错误: " + ex.Message);
            }
           
        }
        void Uncompress(string zipFile, string extractPath)
        {
            // 设置打开的ZIP文件的编码为GBK
            using (var zip = ZipFile.Open(zipFile, ZipArchiveMode.Read, Encoding.GetEncoding("GBK")))
            {
                foreach (var entry in zip.Entries)
                {
                    System.Console.WriteLine(entry.FullName);
                }
                zip.ExtractToDirectory(extractPath, true);
            }
        }
        string  return0ByEmpt(string info)
        {
            if(info==null || info.Equals(""))
            {
                return "0";
            }
            else
            {
                return info;
            }
        }

    }
}
